package me.baicai.weixin.identity.repository;

import com.alibaba.fastjson.JSONObject;
import me.baicai.weixin.entity.identity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;


public interface EmployeeRepository extends JpaRepository<Employee, Long>
{
    @Query(value = "select * from (select i.id, e.`name`, e.telphone,i.avatarUrl,count(s.id) as shop from employee e LEFT JOIN identity i on e.openid = i.openid  LEFT JOIN shop s on s.engId = e.openid or s.salesmanId =  e.openid where e.agentId = ?1 and e.type = ?2 GROUP BY e.id,i.id) as a, (select i.id as iii,count(f.id) as recordCount  from employee e LEFT JOIN identity i on e.openid = i.openid  LEFT JOIN shop s on s.engId = e.openid or s.salesmanId =  e.openid LEFT JOIN fault_record f on f.shopId = s.id and f.`status` = '1'  where e.agentId = ?1 and e.type = ?2 GROUP BY e.id,i.id) as b where a.id = b.iii", nativeQuery = true)
    List<JSONObject> getAllByAgentIdAndType(String openid, String type);

    boolean existsByOpenid(String openid);

    @Query(value = "select * from employee where openid = (select i.openid from identity i where i.id = ?1)" , nativeQuery = true)
    Employee findById(Integer id);

    Employee findEmployeeByOpenid(String openid);

    Employee getById(Integer id);

    @Query(value = "select s.engCommission, t.id, e.`name`, t.avatarUrl,e.telphone from shop s , identity t , employee e where t.openid = s.engId and e.openid = t.openid and s.id = ?1", nativeQuery = true)
    JSONObject shopEng(Integer id);

    @Query(value = "select s.salesmanCommission, t.id, e.`name`, t.avatarUrl,e.telphone from shop s , identity t , employee e where t.openid = s.salesmanId and e.openid = t.openid and s.id = ?1", nativeQuery = true)
    JSONObject shopSal(Integer id);

    @Query(value = "select * from (select i.id, e.`name`, e.type, e.telphone,i.avatarUrl,count(s.id) as shop from employee e LEFT JOIN identity i on e.openid = i.openid  LEFT JOIN shop s on s.engId = e.openid or s.salesmanId =  e.openid where e.agentId = ?1  GROUP BY e.id,i.id) as a, (select i.id as iii,count(f.id) as recordCount  from employee e LEFT JOIN identity i on e.openid = i.openid  LEFT JOIN shop s on s.engId = e.openid or s.salesmanId =  e.openid LEFT JOIN fault_record f on f.shopId = s.id and f.`status` = '1'  where e.agentId = ?1 GROUP BY e.id,i.id) as b where a.id = b.iii", nativeQuery = true)
    List<JSONObject> findAllByAgentId(String id);
}
